package team;


import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Comparator;

/**
 * Fetch the result by query statement, return ArrayLists
 * @author jerry wu, Sean Peng
 *
 */
public class Result implements Comparator<String>{
	
	public static String table_RADCRX = "RADCRX";
	public static String table_RDIMARS = "RDIMARS";
	
	
	/**
	 * Set a private constructor
	 */
	private Result() {
		
	}

	
	/**
	 * Compare 2 Strings
	 * @param o1 string to compare
	 * @param o2 String to compare
	 * @return positive number if o1 > o2, negative number if o1 < o2, 0 if o1 = o2
	 */
	@Override
	public int compare(String o1, String o2) {
		return o1.compareTo(o2);
	}
	
	/**
	 * Get an ArrayList of of AutoMaker
	 * @return AutoMaker List
	 */
	public static String[] getAutoMaker() {
		ArrayList<String> mList = new ArrayList<String>();
		try {
			DBOperation dbop = new DBOperation();
			ResultSet rs = dbop.queryMaker();
			mList = constructList(rs);
			Collections.sort(mList, new Result());
			dbop.disconnectFromDB();
		} catch (SQLException e) {
			System.err.println(e.getMessage());
		}

		return mList.toArray(new String[mList.size()]);
	}

	
	/**
	 * Get a list of model according to auto maker
	 * @param maker Auto Maker
	 * @return a list of model
	 */
	public static String[] getModel(String maker) {
		ArrayList<String> mList = new ArrayList<String>();
		try {
			DBOperation dbop = new DBOperation();
			ResultSet rs = dbop.queryModel(maker);
			mList = constructList(rs);
			Collections.sort(mList, new Result());
			dbop.disconnectFromDB();
		} catch (SQLException e) {
			System.err.println("Error in Result.getModel: " + e.getMessage());
		}
		return mList.toArray(new String[mList.size()]);
	}

	
	/**
	 * Get a list of year according to auto maker, model
	 * @param maker auto maker
	 * @param model auto model
	 * @return a list of year
	 */
	public static String[] getYear(String maker, String model) {
		ArrayList<String> yl = new ArrayList<String>();
		try {
			DBOperation dbop = new DBOperation();
			ResultSet rs = dbop.queryYear(maker, model);
			yl = constructList(rs);
			Collections.sort(yl, new Result());
			dbop.disconnectFromDB();
		} catch (SQLException e) {
			System.err.println("Error in Result.getYear: " + e.getMessage());
		}
		return yl.toArray(new String[yl.size()]);
	}

	
	/**
	 * Get a list of engine type according to auto maker, model, and year
	 * @param maker auto maker
	 * @param model car model
	 * @param year build year
	 * @return a list of engine
	 */
	public static String[] getEngineType(String maker, String model, String year) {
		ArrayList<String> et = new ArrayList<String>();
		try {
			DBOperation dbop = new DBOperation();
			ResultSet rs = dbop.queryEngineType(maker, model, year);
			et = constructList(rs);
			Collections.sort(et, new Result());
			dbop.disconnectFromDB();
		} catch (SQLException e) {
			System.err.println("Error in Result.getEngineType: " + e.getMessage());
		}
		return et.toArray(new String[et.size()]);
	}

	/**
	 * Get the Description table according to maker, model, and year
	 * @param maker Auto Maker
	 * @param model Auto Model
	 * @param year	make Year
	 * @return 2 dimensional array stores table with attributes: DESCRIPTION, LITRES, ENGINE_TYPE, CUBIC_INCHES, RLINK
	 * @throws SQLException
	 */
	public static String[] getAllDesc(String maker, String model, String year) throws SQLException{
		DBOperation dbop = new DBOperation();
		ResultSet rs = dbop.queryDescription(maker, model, year);
		ResultSetMetaData rsmd = rs.getMetaData();
		
		int row_size = 0;
		int col_size = rsmd.getColumnCount();
		if (rs != null) {
			rs.beforeFirst();
			rs.last();
			row_size = rs.getRow();
			rs.beforeFirst();
		}
		String[][] table = new String[row_size][col_size];
		
		int index = 0;
		while (rs.next()) {
			for (int j = 0; j < col_size; j++) {
				table[index][j] = rs.getString(j+1);
			}
			index++;
		}
		
		/*
		String result = "";
		for (int i = 0; i < row_size; i++ ){
			for(int j = 0; j < col_size; j++){
				result = result + table[i][j] + " ";
			}
			result = result + "\n";
		}
				
		dbop.disconnectFromDB();
		
		return result;
		*/
		
		String[] specArr = new String[row_size];
		
		for(int i=0; i<row_size; i++) {
			
			StringBuilder sb = new StringBuilder();
			
			for(int j=0; j<col_size;j++) {
				
				sb.append(table[i][j] + " ");
			}
			
			specArr[i] = sb.toString();
		}
		dbop.disconnectFromDB();
		return specArr;
		
	}
	
	/**
	 * Add New Car Into DB
	 * @param newAutoInfo New Car Info
 	 * @return true if added successfully; otherwise, false
	 */
	public static boolean createNewAuto(String[] newAutoInfo) {
		boolean added = false;
		try {
			DBOperation dbop = new DBOperation();
			dbop.createNewAuto(newAutoInfo);
//			dbop.u
			dbop.disconnectFromDB();
			added = true;
		} catch (SQLException e) {
			System.err.println("Error in Result.createNewAuto(): " + e.getMessage());
			e.printStackTrace();
		}
		return added;
	}
	
	/**
	 * Edit Auto Information
	 * @param updatedInfo the updated auto info to be stored in the oracle db
	 * @param originalInfo the original auto info
	 * @return true if update successful; otherwise, false
	 */
	public static boolean editAuto(String[] updatedInfo, String[] originalInfo) {
		boolean updated = false;
		try {
			DBOperation dbop = new DBOperation();
			dbop.queryUpdateAuto(updatedInfo, originalInfo);
			dbop.disconnectFromDB();
			updated = true;
		} catch (SQLException e) {
			System.err.println("Error in Result.editAuto(): " + e.getMessage());
		}
		return updated;
	}
	
	/**
	 * Get a list of vendors' name
	 * @return an array of vendor's name
	 */
	public static String[] getVendor() {
		
		ArrayList<String> vList = new ArrayList<String>();
		
		try {
			DBOperation dbop = new DBOperation();
			ResultSet rs = dbop.queryVendor();
			vList = constructList(rs);
			Collections.sort(vList, new Result());
			dbop.disconnectFromDB();
		} catch (SQLException e) {
			System.err.println(e.getMessage());
		}

		return vList.toArray(new String[vList.size()]);
	}
	
	/**
	 * Get a list of available vendors' name by rLink
	 * @param rLink the association number between auto part and vendors
	 * @return
	 */
	public static String[] getVendor(Integer rLink) {
		
		ArrayList<String> temp = new ArrayList<String>();
		ArrayList<String> vList = new ArrayList<String>();
		
		try {
			DBOperation dbop = new DBOperation();
			ResultSet rs = dbop.queryVendor(rLink);
			ResultSetMetaData rsmd = rs.getMetaData();
			
			temp = constructList(rs, rsmd.getColumnCount());
			
			for(int i=0; i<temp.size(); i++) {
				
				if(isNumeric(temp.get(i)))
					vList.add(rsmd.getColumnName(i+1));
			}
			
			dbop.disconnectFromDB();
		} catch (SQLException e) {
			System.err.println(e.getMessage());
		}
		
		return vList.toArray(new String[vList.size()]);
	}
	
	
	/**
	 * Get a list of part number according to part vendor's name from corresponding vendor's table
	 * @param vendor vendor's name
	 * @return an array of part number
	 */
	public static String[] getPartNumber(String vendor) {
		
		ArrayList<String> pl = new ArrayList<String>();
		
		try {
			DBOperation dbop = new DBOperation();
			ResultSet rs = dbop.queryPartNumber(vendor);
			pl = constructList(rs);
			Collections.sort(pl, new Result());
			dbop.disconnectFromDB();
		} catch (SQLException e) {
			System.err.println("Error in Result.getPartNumber: " + e.getMessage());
		}
		
		return pl.toArray(new String[pl.size()]);
	}
	
	
	/**
	 * Get a list of part number based on vendor's name and rLink from table RADCRX
	 * @param vendor vendor's name
	 * @param rLink a number associate auto part and vendor
	 * @return an array of part number
	 */
	public static String[] getPartNumber(String vendor, Integer rLink) {
		
		ArrayList<String> pl = new ArrayList<String>();
		
		try {
			DBOperation dbop = new DBOperation();
			ResultSet rs = dbop.queryPartNumber(vendor, rLink);			
			ResultSetMetaData rsmd = rs.getMetaData();
			pl = constructList(rs, rsmd.getColumnCount());
			Collections.sort(pl, new Result());
			dbop.disconnectFromDB();
		} catch (SQLException e) {
			System.err.println("Error in Result.getPartNumber: " + e.getMessage());
		}
		
		return pl.toArray(new String[pl.size()]);
	}
	
	
	/**
	 * Get a list of part specification according to part vendor's name, part number
	 * @param vendor vendor's name
	 * @param partNumber part number
	 * @return an array of part specification
	 */
	public static String[] getPartSpec(String vendor, String partNumber) {
		
		ArrayList<String> pl = new ArrayList<String>();
		
		try {
			DBOperation dbop = new DBOperation();
			ResultSet rs = dbop.queryPartSpec(vendor, partNumber);			
			ResultSetMetaData rsmd = rs.getMetaData();
			
			pl = constructList(rs, rsmd.getColumnCount());
			dbop.disconnectFromDB();
		} catch (SQLException e) {
			System.err.println("Error in Result.getPartSpec: " + e.getMessage());
		}
		
		return pl.toArray(new String[pl.size()]);
	}
	
	
	/**
	 * Get the column name from the specified table
	 * @param tableName
	 * @return Array of string represent the column name
	 */
	public static String[] getColumnName(String tableName) {
		
		ArrayList<String> colName = new ArrayList<String>();
		
		try {
			DBOperation dbop = new DBOperation();
			ResultSet rs = dbop.queryColumnName(tableName);
			ResultSetMetaData rsmd = rs.getMetaData();
			
			colName = constructList(rs, rsmd.getColumnCount());
			dbop.disconnectFromDB();
		} catch (SQLException e) {
			System.err.println("Error in Result.getColumnName: " + e.getMessage());
		}
		
		return colName.toArray(new String[colName.size()]);
	}
	
	
	/**
	 * Insert part into database
	 * @param vendor vendor's name
	 * @param data part's data from user's input
	 */
	public static boolean insertNewPart(String vendor, ArrayList<String> data) {
		
		try {
			DBOperation dbop = new DBOperation();
			ResultSet rs = dbop.checkExisting(vendor, data.get(0));
			
			if(!rs.next()) {
				dbop.insertNewPart(vendor, data);
				dbop.disconnectFromDB();
			}
			else {
				dbop.disconnectFromDB();
				return false;
			}		
		} catch (SQLException e) {
			System.err.println("Error in Result.insertNewPart: " + e.getMessage());
		}
		
		return true;
	}
	
	
	/**
	 * Insert part into database
	 * @param vendor vendor's name
	 * @param data part's data from user's input
	 * @param rLink
	 */
	public static boolean insertNewPart(String vendor, ArrayList<String> data, Integer rLink) {
		
		try {
			DBOperation dbop = new DBOperation();
			ResultSet rs = dbop.checkExisting(vendor, data.get(0));
			
			if(!rs.next()) {
				dbop.insertNewPart(vendor, data, rLink);
				dbop.updateRADCRX(vendor, data.get(0), rLink);
				dbop.disconnectFromDB();
			}
			else {
				dbop.disconnectFromDB();
				return false;
			}		
		} catch (SQLException e) {
			System.err.println("Error in Result.insertNewPart: " + e.getMessage());
		}
		
		return true;
	}
	
	
	/**
	 * Delete part from database
	 * @param vendor vendor's name
	 * @param partNum part number
	 */
	public static void deletePart(String vendor, String partNum) {
		
		try {
			DBOperation dbop = new DBOperation();
			dbop.deletePart(vendor, partNum);
			dbop.updateRADCRX(vendor, partNum);
			dbop.disconnectFromDB();
		} catch (SQLException e) {
			System.err.println("Error in Result.deletePart: " + e.getMessage());
		}
	}
	
	
	/**
	 * Update the RADCRX table
	 * @param vendor vendor's name
	 * @param partNum part number
	 * @param rLink
	 */
	public static void updateRADCRX(String vendor, String partNum, Integer rLink) {
		
		try {
			DBOperation dbop = new DBOperation();
			dbop.updateRADCRX(vendor, partNum, rLink);
			dbop.disconnectFromDB();
		} catch (SQLException e) {
			System.err.println("Error in Result.deletePart: " + e.getMessage());
		}
	}
	
	
	/**
	 * Update the RADCRX table
	 * @param vendor vendor's name
	 * @param partNum part number
	 * @param rLink
	 */
	public static void updateRADCRX(String vendor, String newPartNum, String oldPartNum) {
		
		try {
			DBOperation dbop = new DBOperation();
			dbop.updateRADCRX(vendor, newPartNum, oldPartNum);
			dbop.disconnectFromDB();
		} catch (SQLException e) {
			System.err.println("Error in Result.deletePart: " + e.getMessage());
		}
	}
	
	
	/**
	 * Update part information
	 * @param vendor vendor's name
	 * @param inputData part information from user's input
	 */
	public static void updatePart(String vendor, String oldPartNum, ArrayList<String> inputData) {
		
		String partNum = inputData.get(0);
		
		try {
			DBOperation dbop = new DBOperation();
			dbop.updatePart(vendor, partNum, inputData);
			dbop.updateRADCRX(vendor, partNum, oldPartNum);
			dbop.disconnectFromDB();
		} catch (SQLException e) {
			System.err.println("Error in Result.updatePart: " + e.getMessage());
		}
	}
	
	/**
	 * Create a new Table for a new maker
	 * @param maker new table name
	 * @return true if create successfully; otherwise, false
	 */
	public static boolean createMaker(String maker) {
		boolean created = false;
		try {
			DBOperation dbop = new DBOperation();
			dbop.createMaker(maker);
			dbop.disconnectFromDB();
			created = true;
		} catch (SQLException e) {
			System.err.println("Error in Result.CreateMaker: " + e.getMessage());
		}
		return created;
	}
	
	/**
	 * Get the current maximum of rLink, for creating a new rLink number
	 * @return maximum of rLink
	 */
	public static int getMaxRLink() {
		int max = 0;
		try {
			DBOperation dbop = new DBOperation();
			ResultSet rs = dbop.getMaxRLink();
			while(rs.next()) 
				max = rs.getInt(1);
			dbop.disconnectFromDB();
		} catch (SQLException e) {
			System.err.println("Error in Result.getMaxRLink(): " + e.getMessage());
			e.printStackTrace();
		}
		return max;
	}
	
	/**
	 * Check if the part exist in the given vendor
	 * @param vendor
	 * @param partNum
	 * @return
	 */
	public static boolean checkExisting(String vendor, String partNum) {
		
		try {
			DBOperation dbop = new DBOperation();
			ResultSet rs = dbop.checkExisting(vendor, partNum);
			
			if(rs.next()) {
				dbop.disconnectFromDB();
			}
			else {
				dbop.disconnectFromDB();
				return false;
			}		
		} catch (SQLException e) {
			System.err.println("Error in Result.checkExisting: " + e.getMessage());
		}
		
		return true;
	}
	
	/**
	 * Add a new rLink to RADCRX when create a new auto
	 * @param rLink new rLink to be added
	 * @return true if added successful; otherwise false
	 */
	public static boolean addNewRLink(String rLink) {
		boolean added = false;
		try {
			DBOperation dbop = new DBOperation();
			dbop.addNewRLink(rLink);
			dbop.disconnectFromDB();
			added = true;
		} catch (SQLException e) {
			System.out.println("Error in Result.addNewRLink(): " + e.getMessage());
			e.printStackTrace();
		}
		return added;
	}
	
	
	/**
	 * Construct the ArrayList with no duplicate items by the result set from oracle
	 * @param rs contains list items fetched from oracle
	 * @return ArrayList with no duplicate items
	 */
	private static ArrayList<String> constructList(ResultSet rs) throws SQLException{
		ArrayList<String> list = new ArrayList<String>();
		while (rs.next()) {
			if (!list.contains(rs.getString(1)))
				list.add(rs.getString(1));
		}
		return list;
	}
	
	/**
	 * Construct the ArrayList based on the returned result set and its column size from database
	 * @param rs contains list items fetched from oracle
	 * @param columnCount the size of the column in result set
	 * @return ArrayList based on the data from result set
	 */
	private static ArrayList<String> constructList(ResultSet rs, int columnCount) throws SQLException{
		
		ArrayList<String> list = new ArrayList<String>();
		
		while (rs.next()) {			
			for(int i=0; i<columnCount; i++) {			
				list.add(rs.getString(i+1));
			}		
		}
		
		return list;
	}
	
	
	/**
	 * Check if the given string contains numeric character only
	 * @param s the given string
	 * @return true/false
	 */
	private static boolean isNumeric(String s) {
		
		try {
			Integer.parseInt(s);
		} catch(NumberFormatException ex) {
			return false;
		}
		
		return true;
	}
	
	public static boolean deleteAuto(String deleteQuery){
		
		boolean deleted = false; 
		try {
			DBOperation dbop = new DBOperation();
			dbop.deleteAuto(deleteQuery);
			dbop.disconnectFromDB();
			deleted = true;
		} catch (SQLException e) {
			System.out.println("Error in Result.deleteAuto(): " + e.getMessage());
			e.printStackTrace();
		}
		return deleted;
	}
	
}